package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.SimpleResult;
import utility.Utility;

public class RaceDataRetriever {
	public static List<SimpleResult> fetchResultsForPerson(int personId) {
		System.out.println("fetchResultsForPerson begins") ;
		ArrayList<SimpleResult> raceResults = new ArrayList<SimpleResult>(10) ; 
		Connection connection = Database.fetchMySqlConnection(Utility.getProperties()) ;
		Statement statement = null ;  
		String fetchSqlString = "select rs.time, rc.year, p.name, p.birth_year, p.gender from running.result rs " ;
		fetchSqlString += " join running.race rc on rc.id = rs.raceId join running.person p on p.id = rs.personId where p.id = " + personId ;
		try {
			statement =  connection.createStatement() ;
			ResultSet fetchResultSet = statement.executeQuery(fetchSqlString) ; 
			while(fetchResultSet.next()) {
				int time = fetchResultSet.getInt("time") ; 
				int year = fetchResultSet.getInt("year") ; 
				String name = fetchResultSet.getString("name") ; 
				int birthYear = fetchResultSet.getInt("birth_year") ; 
				String gender = fetchResultSet.getString("gender") ; 
				SimpleResult simpleResult = new SimpleResult(time, year, name, birthYear, gender) ;
				raceResults.add(simpleResult) ; 
			}
		} 
		catch (SQLException exception) {
			System.out.println(exception.getMessage()) ;
			System.out.println("fetchSqlString: " + fetchSqlString) ;
		}
		finally {
			System.out.println("fetchResultsForPerson finally.") ;
			try {
				if(statement != null) {
					statement.close() ;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		return raceResults ; 
	}
	
	public static void main(String[] arguments) {
		List<SimpleResult> thomResults = RaceDataRetriever.fetchResultsForPerson(13613) ;
		for(int i = 0 ; i < thomResults.size() ; i++) {
			System.out.println(i + " = " + thomResults.get(i)) ; 
		}
	}
}
